﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.VehicleCategoryReference;
using Dapper;

namespace BCData.TMS.VehicleCategoryReference
{
    public class VehicleCategoryReferenceData : IVehicleCategoryReferenceData
    {
        #region 私有变量

        private readonly IDatabaseContext database;
        private readonly string QUERY = "SELECT VehicleCategoryReferenceId,VehicleCategory,VehicleTypeId FROM vehiclecategoryreference WHERE 1=1 ";

        #endregion 私有变量

        #region 构造函数

        public VehicleCategoryReferenceData(IDatabaseContext database)
        {
            this.database = database;
        }

        #endregion 构造函数

        #region 公共方法

        public async Task<int> InserAsync(List<VehicleCategoryReferenceEntity> entities)
        {
            //var sql = SqlCommands.GetInsertSql("vehiclecategoryreference", new[] { "VehicleTypeId", "VehicleCategory " });
            //return await sqlCommand.ExecuteMultipleAsync<VehicleCategoryReferenceEntity>(sql, CommandType.Text, entity);
            return await database.Master.InsertAllAsync(entities);
        }

        public async Task<int> SingleInsertAsync(VehicleCategoryReferenceEntity entity)
        {
            var sql = SqlCommands.GetInsertSql("vehiclecategoryreference", new[] { "VehicleTypeId", "VehicleCategory " });
            sql = string.Format("{0} {1}", sql, "SELECT LAST_INSERT_ID()");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", entity.VehicleTypeId, DbType.Int32);
            parameters.Add("?VehicleCategory", entity.VehicleCategory, DbType.AnsiString);
            //var data = await databaseContext.Master.ExecuteScalarAsync(sql, parameters, databaseContext.Transaction);
            var data = await database.Master.ExecuteScalarAsync(sql, parameters);
            return data.ToInt32();
        }

        public async Task<int> UpdateAsync(List<VehicleCategoryReferenceEntity> entities)
        {
            int i = 0;
            foreach (var entity in entities)
            {
                var sql = SqlCommands.GetUpdateSql("vehiclecategoryreference", new[] { "Enabled" },
                   new[] { "VehicleTypeId", "VehicleCategory" });
                DynamicParameters parameter = new DynamicParameters();
                parameter.Add("?Enabled", true, DbType.Boolean);
                parameter.Add("?VehicleTypeId", entity.VehicleTypeId, DbType.Int64, size: 20);
                parameter.Add("?VehicleCategory", entity.VehicleCategory.ToString(), DbType.AnsiString);
                i += await database.Master.ExecuteAsync(sql, parameter);
            }
            return i;
        }

        public async Task<VehicleCategoryReferenceEntity> GetAsync(int vehicleTypeId, string vehicleCategory)
        {
            string sql = $"{QUERY} AND VehicleTypeId=?VehicleTypeId AND VehicleCategory=?VehicleCategory";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleCategory", vehicleCategory, DbType.AnsiString);
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);

            //var data = await databaseContext.Master.QueryAsync<VehicleCategoryReferenceEntity>(sql, parameters);
            //return data.FirstOrDefault();
            return await database.Slave.QueryFirstOrDefaultAsync<VehicleCategoryReferenceEntity>(sql, parameters);
        }

        public async Task<int> DeleteAsync(int vehiclecategoryreferenceId)
        {
            string sql = "DELETE FROM vehiclecategoryreference where VehicleCategoryReferenceId=?VehicleCategoryReferenceId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleCategoryReferenceId", vehiclecategoryreferenceId, DbType.Int32);
            //return await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return await database.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<int> DeleteByVehicleTypeIdAsync(int vehicleTypeId)
        {
            string sql = "UPDATE vehiclecategoryreference SET Enabled=0 WHERE VehicleTypeId=?VehicleTypeId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            //return await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return await database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }

        public async Task<IEnumerable<VehicleCategoryReferenceExpandEntity>> GetByVehicleTypeId(int? vehicleTypeId, string vehicleCategory)
        {
            string sql = @"SELECT A.vehiclecategoryreferenceId,A.VehicleTypeId,A.VehicleCategory,B.ItemValue AS CategoryValue 
                            FROM vehiclecategoryreference A
                                INNER JOIN  dictionaryreference B on A.VehicleCategory=B.SearchKey";
            DynamicParameters parameters = new DynamicParameters();
            var wheres = new List<string>();
            if (vehicleTypeId.HasValue)
            {
                wheres.Add("A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int32);
            }

            if (!string.IsNullOrWhiteSpace(vehicleCategory))
            {
                wheres.Add("A.VehicleCategory=?VehicleCategory");
                parameters.Add("?VehicleCategory", vehicleCategory, DbType.AnsiString);
            }

            sql = string.Format("{0} {1}", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            //var data = await databaseContext.Master.QueryAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
            //return data.ToList();
            return await database.Slave.QueryAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<VehicleCategoryReferenceExpandEntity>, int)> GetAll(int pageIndex, int pageSize)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?pageIndex", pageIndex);
            parameters.Add("?pageSize", pageSize);

            var sql = @"SELECT
                             A.VehicleCategoryReferenceId,
                             A.VehicleCategory,
                             A.VehicleTypeId ,
                             B.VehicleTypeName
                        FROM
                            vehiclecategoryreference A INNER JOIN  vehicletype B ON A.VehicleTypeId = B.VehicleTypeId
                        WHERE
                        1 = 1 LIMIT ?pageIndex,?pageSize;";
            sql = $"{sql} SELECT COUNT(1) vehiclecategoryreference ";

            //var data = await databaseContext.Master.QueryMultipleAsync<VehicleCategoryReferenceExpandEntity, int>(sql, parameters);

            //return (data.Item1.ToList(), data.Item2);

            return await database.Slave.QueryPageableListAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
        }

        public async Task<VehicleCategoryReferenceExpandEntity> GetById(int vehicleCategoryReferenceId)
        {
            string sql = @"SELECT
	                    A.vehiclecategoryreferenceId,
	                    A.VehicleTypeId,
	                    A.VehicleCategory,
	                    B.ItemValue AS CategoryValue,
	                    C.VehicleTypeName
                    FROM
	                    vehiclecategoryreference A
	                    INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
	                    INNER JOIN vehicletype C ON A.VehicleTypeId=C.VehicleTypeId
	                    WHERE	 A.VehicleCategoryReferenceId =?VehicleCategoryReferenceId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId, DbType.Int32);
            //return await databaseContext.Slave.QueryFirstOrDefaultAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
            return await database.Slave.QueryFirstOrDefaultAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
        }

        public async Task<IEnumerable<VehicleCategoryReferenceExpandEntity>> GetVehicleCategoryReferenceExpandEntity(int vehicleTypeId)
        {
            string sql = @"SELECT
	                        A.vehiclecategoryreferenceId,
	                        A.VehicleTypeId,
	                        A.VehicleCategory,
	                        B.ItemValue AS CategoryValue,
	                        C.VehicleTypeName
                        FROM
	                        vehiclecategoryreference A
	                        INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
	                        INNER JOIN vehicletype C ON A.VehicleTypeId=C.VehicleTypeId
                            WHERE A.VehicleTypeId=?VehicleTypeId ORDER BY A.VehicleCategory DESC";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            //return await databaseContext.Slave.QueryAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
            return await database.Slave.QueryAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
        }

        public async Task<IEnumerable<VehicleCategoryReferenceExpandEntity>> GetCategoryReferenceList(int? vehicleTypeId)
        {
            string sql = @" SELECT
	                        A.vehiclecategoryreferenceId,
	                        A.VehicleTypeId,
	                        A.VehicleCategory,
	                        B.ItemValue AS CategoryValue,
	                        C.VehicleTypeName
                        FROM
	                        vehiclecategoryreference A
	                        INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
	                        INNER JOIN vehicletype C ON A.VehicleTypeId=C.VehicleTypeId";
            DynamicParameters parameters = new DynamicParameters();
            if (vehicleTypeId.HasValue)
            {
                sql = string.Format("{0} WHERE A.VehicleTypeId = ?VehicleTypeId", sql);
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int32);
            }
            //var data = await databaseContext.Slave.QueryAsync<VehicleCategoryReferenceExpandEntity>(sql, CommandType.Text);
            //return data.ToList();
            return await database.Slave.QueryAsync<VehicleCategoryReferenceExpandEntity>(sql, parameters);
        }

        public async Task<VehicleCategoryReferenceEntity> SwitchCategory(int vehicleCategoryReferenceId, int vehicleTypeId)
        {
            string sql = @"SELECT VehicleCategoryReferenceId,VehicleCategory,VehicleTypeId 
                            FROM vehiclecategoryreference 
                           WHERE VehicleTypeId=?VehicleTypeId
                            AND VehicleCategory=(
                            SELECT VehicleCategory 
                            FROM vehiclecategoryreference 
                            WHERE VehicleCategoryReferenceId=?VehicleCategoryReferenceId);";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId, DbType.Int32);
            //return await databaseContext.Master.QueryFirstOrDefaultAsync<VehicleCategoryReferenceEntity>(sql, parameters);
            return await database.Slave.QueryFirstOrDefaultAsync<VehicleCategoryReferenceEntity>(sql, parameters);
        }

        #endregion 公共方法
    }
}